from app.utils.mysql_util import get_connection

def insert_salesperson(salesperson_data):
    """创建或更新销售员信息"""
    with get_connection() as conn:
        with conn.cursor() as cursor:
            sql = """
                INSERT INTO salesperson (
                    employee_no, name, mobile, email, 
                    created_at, updated_at, status
                ) VALUES (
                    %(employee_no)s, %(name)s, %(mobile)s, %(email)s,
                    NOW(), NOW(), 1
                ) ON DUPLICATE KEY UPDATE
                    name = VALUES(name),
                    mobile = VALUES(mobile),
                    email = VALUES(email),
                    updated_at = NOW()
            """
            cursor.execute(sql, salesperson_data)
        conn.commit()

def delete_salesperson(employee_no):
    """删除销售员（软删除）"""
    with get_connection() as conn:
        with conn.cursor() as cursor:
            sql = """
                UPDATE salesperson 
                SET status = 0, updated_at = NOW()
                WHERE employee_no = %s
            """
            cursor.execute(sql, (employee_no,))
        conn.commit()

def get_salesperson_list(page=1, page_size=20, search_key=None):
    """获取销售员列表"""
    with get_connection() as conn:
        with conn.cursor() as cursor:
            where_clause = "WHERE status = 1"
            params = []
            
            if search_key:
                where_clause += " AND (name LIKE %s OR mobile LIKE %s OR employee_no LIKE %s)"
                search_pattern = f"%{search_key}%"
                params.extend([search_pattern, search_pattern, search_pattern])
            
            count_sql = f"SELECT COUNT(*) FROM salesperson {where_clause}"
            cursor.execute(count_sql, params)
            total = cursor.fetchone()[0]
            
            offset = (page - 1) * page_size
            sql = f"""
                SELECT employee_no, name, mobile, email, created_at, updated_at
                FROM salesperson 
                {where_clause}
                ORDER BY created_at DESC
                LIMIT %s OFFSET %s
            """
            params.extend([page_size, offset])
            cursor.execute(sql, params)
            salespersons = cursor.fetchall()
            
            return {
                "total": total,
                "items": salespersons,
                "page": page,
                "page_size": page_size
            }

def get_salesperson_detail(employee_no):
    """获取销售员详情及其关联的顾客"""
    with get_connection() as conn:
        with conn.cursor() as cursor:
            # 获取销售员基本信息
            sql = """
                SELECT employee_no, name, mobile, email, created_at, updated_at
                FROM salesperson
                WHERE employee_no = %s AND status = 1
            """
            cursor.execute(sql, (employee_no,))
            salesperson = cursor.fetchone()
            
            if not salesperson:
                return None
            
            # 获取关联的顾客列表
            sql = """
                SELECT c.customer_no, c.name, c.mobile, c.email
                FROM customers c
                JOIN salesperson_customer sc ON c.customer_no = sc.customer_no
                WHERE sc.employee_no = %s AND sc.status = 1
            """
            cursor.execute(sql, (employee_no,))
            customers = cursor.fetchall()
            
            return {
                "salesperson": salesperson,
                "customers": customers
            }

def link_customer(employee_no, customer_no):
    """关联销售员和顾客"""
    with get_connection() as conn:
        with conn.cursor() as cursor:
            sql = """
                INSERT INTO salesperson_customer (
                    employee_no, customer_no, created_at, updated_at, status
                ) VALUES (
                    %s, %s, NOW(), NOW(), 1
                ) ON DUPLICATE KEY UPDATE
                    status = 1,
                    updated_at = NOW()
            """
            cursor.execute(sql, (employee_no, customer_no))
        conn.commit()

def unlink_customer(employee_no, customer_no):
    """解除销售员和顾客的关联"""
    with get_connection() as conn:
        with conn.cursor() as cursor:
            sql = """
                UPDATE salesperson_customer
                SET status = 0, updated_at = NOW()
                WHERE employee_no = %s AND customer_no = %s
            """
            cursor.execute(sql, (employee_no, customer_no))
        conn.commit() 